package com.starbaba.stat.service.impl;

import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

import com.base.common.database.BaseDao;
import com.starbaba.core.web.page.PageRequest;
import com.starbaba.core.web.page.PageResponse;
import com.starbaba.stat.model.StatActivityModel;
import com.starbaba.stat.service.StatActivityService;
import com.starbaba.stat.utils.Constant;

@SuppressWarnings("rawtypes")
@Component
public class StatActivityServiceImpl implements StatActivityService {

	static BaseDao dao = new BaseDao(Constant.LOAN_STAT_DB_POOL);// Constant.LOAN_STAT_DB_POOL
	
	@Override
	public void showPage(Map<String, Object> paramsMap) throws Exception {
		
//		List<StatActivityModel> prdList = dao.list("select distinct a.app_prd_id, b.name prd_name from stat.stat_prd_main_click a, loan.comm_product_prdid b where a.app_prd_id = b.prdid ", StatActivityModel.class);
//		List<StatActivityModel> channelList = dao.list("select distinct a.channel, b.channel_name channel_name from stat.stat_prd_main_click a, loan.comm_product_channel b where a.channel = b.channel_code ", StatActivityModel.class);

		List<StatActivityModel> prdList = dao.list("SELECT prdid, name FROM comm_product_prdid ;", StatActivityModel.class);
		List<StatActivityModel> channelList = dao.list("SELECT channel_code , channel_name  FROM comm_product_channel ;", StatActivityModel.class);

		
		paramsMap.put("prdList", prdList);
		paramsMap.put("channelList", channelList);

	}

	@Override
	public List getList(Integer start, Integer length, PageResponse pageResponse, Map<String, Object> paramsMap) throws Exception {
		
		String startTime = (String) paramsMap.get("startTime");
		String endTime = (String) paramsMap.get("endTime");
		String platform = (String) paramsMap.get("platform");
		String prd_id = (String) paramsMap.get("prd_id");
		String channel = (String) paramsMap.get("channel");
		String user_type = (String) paramsMap.get("user_type");
		
		StringBuilder whereParams = new StringBuilder();
		if (StringUtils.isNotEmpty(startTime)) {
			whereParams.append("and a.time >= '" + startTime + "' ");
		}
		if (StringUtils.isNotEmpty(endTime)) {
			whereParams.append("and a.time <= '" + endTime + "' ");
		}
		if (StringUtils.isNotEmpty(platform)) {
			whereParams.append("and a.platform = '" + platform + "' ");
		}
		if (StringUtils.isNotEmpty(prd_id)) {
			whereParams.append("and a.app_prd_id = '" + prd_id + "' ");
		}
		if (StringUtils.isNotEmpty(channel)) {
			whereParams.append("and a.channel = '" + channel + "' ");
		}
		if (StringUtils.isNotEmpty(user_type)) {
			whereParams.append("and a.user_type = '" + user_type + "' ");
		}
		String whereParamsString = whereParams.toString();
		
		StringBuilder sqlMainUv = new StringBuilder("SELECT UV AS position1 FROM stat_prd_main_click WHERE position = 0 and time = a.time and user_type = a.user_type ");
		StringBuilder sqlLoanUv = new StringBuilder("SELECT UV AS position2 FROM stat_prd_main_click WHERE position = 1 and time = a.time and user_type = a.user_type ");
		StringBuilder sqlCardUv = new StringBuilder("SELECT UV AS position3 FROM stat_prd_main_click WHERE position = 2 and time = a.time and user_type = a.user_type ");
		StringBuilder sqlMineUv = new StringBuilder("SELECT UV AS position4 FROM stat_prd_main_click WHERE position = 3 and time = a.time and user_type = a.user_type ");

		sqlMainUv.append("and platform = a.platform ");
		sqlLoanUv.append("and platform = a.platform ");
		sqlCardUv.append("and platform = a.platform ");
		sqlMineUv.append("and platform = a.platform ");
		sqlMainUv.append("and app_prd_id = a.app_prd_id ");
		sqlLoanUv.append("and app_prd_id = a.app_prd_id ");
		sqlCardUv.append("and app_prd_id = a.app_prd_id ");
		sqlMineUv.append("and app_prd_id = a.app_prd_id ");
		sqlMainUv.append("and channel = a.channel ");
		sqlLoanUv.append("and channel = a.channel ");
		sqlCardUv.append("and channel = a.channel ");
		sqlMineUv.append("and channel = a.channel ");
		
		StringBuilder sqlAllUv = new StringBuilder("SELECT a.time, a.platform, a.app_prd_id, cpp.`name` prd_name, a.channel, cpc.channel_name channel_name, a.user_type, a.activity_uv AS UV, (");
		sqlAllUv.append(sqlMainUv.toString() + whereParamsString + ") position1, (");
		sqlAllUv.append(sqlLoanUv.toString() + whereParamsString + ") position2, (");
		sqlAllUv.append(sqlCardUv.toString() + whereParamsString + ") position3, (");
		sqlAllUv.append(sqlMineUv.toString() + whereParamsString + ") position4   ");
		sqlAllUv.append("FROM stat.stat_prd_main_click AS a left join loan.comm_product_prdid cpp on a.app_prd_id = cpp.prdid ");
		sqlAllUv.append("left join loan.comm_product_channel cpc on a.channel = cpc.channel_code WHERE 1=1 ");
		sqlAllUv.append(whereParamsString).append("group by time, platform, app_prd_id, channel, user_type order by time desc ");
		
		String sqlCount = "select count(1) from (" + sqlAllUv.toString() + ") b ";
		if (start != null && length != null) {
			sqlAllUv.append("limit " + start + ", " + length);
		}
		
		int count = dao.getInt(sqlCount);
		List<StatActivityModel> list = dao.list(sqlAllUv.toString(), StatActivityModel.class);
		for (StatActivityModel statActivityModel : list) {
			if (StringUtils.isEmpty(statActivityModel.getPlatform())) {
				statActivityModel.setPlatform("未知");
			}
			if (StringUtils.isEmpty(statActivityModel.getPrd_name())) {
				statActivityModel.setPrd_name(statActivityModel.getApp_prd_id());
			}
			if (StringUtils.isEmpty(statActivityModel.getChannel_name())) {
				statActivityModel.setChannel_name(statActivityModel.getChannel());
			}
			
			if ("all".equals(platform)) {
				statActivityModel.setPlatform("全部");
			}
			if ("all".equals(prd_id)) {
				statActivityModel.setPrd_name("全部");
			}
			if ("all".equals(channel)) {
				statActivityModel.setChannel_name("全部");
			}
		}
		pageResponse.setITotalRecords(count);
		pageResponse.setITotalDisplayRecords(count);
		
		return list;
	}
	
	public List<StatActivityModel> getExcelList(Map<String, Object> paramsMap) throws Exception {
		
		String startTime = (String) paramsMap.get("startTime");
		String endTime = (String) paramsMap.get("endTime");
		String platform = (String) paramsMap.get("platform");
		String prd_id = (String) paramsMap.get("prd_id");
		String channel = (String) paramsMap.get("channel");
		String user_type = (String) paramsMap.get("user_type");
		
		StringBuilder whereParams = new StringBuilder();
		if (StringUtils.isNotEmpty(startTime)) {
			whereParams.append("and a.time >= '" + startTime + "' ");
		}
		if (StringUtils.isNotEmpty(endTime)) {
			whereParams.append("and a.time <= '" + endTime + "' ");
		}
		if (StringUtils.isNotEmpty(platform)) {
			whereParams.append("and a.platform = '" + platform + "' ");
		}
		if (StringUtils.isNotEmpty(prd_id)) {
			whereParams.append("and a.app_prd_id = '" + prd_id + "' ");
		}
		if (StringUtils.isNotEmpty(channel)) {
			whereParams.append("and a.channel = '" + channel + "' ");
		}
		if (StringUtils.isNotEmpty(user_type)) {
			whereParams.append("and a.user_type = '" + user_type + "' ");
		}
		String whereParamsString = whereParams.toString();
		
		StringBuilder sqlMainUv = new StringBuilder("SELECT UV AS position1 FROM stat_prd_main_click WHERE position = 0 and time = a.time and user_type = a.user_type ");
		StringBuilder sqlLoanUv = new StringBuilder("SELECT UV AS position2 FROM stat_prd_main_click WHERE position = 1 and time = a.time and user_type = a.user_type ");
		StringBuilder sqlCardUv = new StringBuilder("SELECT UV AS position3 FROM stat_prd_main_click WHERE position = 2 and time = a.time and user_type = a.user_type ");
		StringBuilder sqlMineUv = new StringBuilder("SELECT UV AS position4 FROM stat_prd_main_click WHERE position = 3 and time = a.time and user_type = a.user_type ");

		sqlMainUv.append("and platform = a.platform ");
		sqlLoanUv.append("and platform = a.platform ");
		sqlCardUv.append("and platform = a.platform ");
		sqlMineUv.append("and platform = a.platform ");
		sqlMainUv.append("and app_prd_id = a.app_prd_id ");
		sqlLoanUv.append("and app_prd_id = a.app_prd_id ");
		sqlCardUv.append("and app_prd_id = a.app_prd_id ");
		sqlMineUv.append("and app_prd_id = a.app_prd_id ");
		sqlMainUv.append("and channel = a.channel ");
		sqlLoanUv.append("and channel = a.channel ");
		sqlCardUv.append("and channel = a.channel ");
		sqlMineUv.append("and channel = a.channel ");
		
		StringBuilder sqlAllUv = new StringBuilder("SELECT a.time, a.platform, a.app_prd_id, cpp.`name` prd_name, a.channel, cpc.channel_name channel_name, a.user_type, a.activity_uv AS UV, (");
		sqlAllUv.append(sqlMainUv.toString() + whereParamsString + ") position1, (");
		sqlAllUv.append(sqlLoanUv.toString() + whereParamsString + ") position2, (");
		sqlAllUv.append(sqlCardUv.toString() + whereParamsString + ") position3, (");
		sqlAllUv.append(sqlMineUv.toString() + whereParamsString + ") position4   ");
		sqlAllUv.append("FROM stat.stat_prd_main_click AS a left join loan.comm_product_prdid cpp on a.app_prd_id = cpp.prdid ");
		sqlAllUv.append("left join loan.comm_product_channel cpc on a.channel = cpc.channel_code WHERE 1=1 ");
		sqlAllUv.append(whereParamsString).append("group by time, platform, app_prd_id, channel, user_type order by time desc ");
		
		List<StatActivityModel> list = dao.list(sqlAllUv.toString(), StatActivityModel.class);
		for (StatActivityModel statActivityModel : list) {
			if (StringUtils.isEmpty(statActivityModel.getPlatform())) {
				statActivityModel.setPlatform("未知");
			}
			if (StringUtils.isEmpty(statActivityModel.getPrd_name())) {
				statActivityModel.setPrd_name(statActivityModel.getApp_prd_id());
			}
			if (StringUtils.isEmpty(statActivityModel.getChannel_name())) {
				statActivityModel.setChannel_name(statActivityModel.getChannel());
			}
			
			if ("all".equals(platform)) {
				statActivityModel.setPlatform("全部");
			}
			if ("all".equals(prd_id)) {
				statActivityModel.setPrd_name("全部");
			}
			if ("all".equals(channel)) {
				statActivityModel.setChannel_name("全部");
			}
		}
	
		return list;
	}
}